package jmind.core.poi;


import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.google.common.collect.Maps;
import jmind.base.lang.TallyMap;
import jmind.base.util.DataUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * description:  通用级联处理handler
 *   首先你要有两个实体类，一个是用于存放国家省市区的数据、一个是用于生成Excel时，可以简易的指定表头和大小。
 *     然后基于实现SheetWriteHandler，创建一个自定义的拦截器，这个拦截器的实现，可以完成很多Excel中复杂的实现，比如下拉框、单元格合并，各类样式都可以在这个拦截器中自定义完成，本次只阐述联动下拉框。
 *     在拦截器中，一共分为三步：
 *         第一步：先将国家省市区的数据分别写入到不同的sheet页，并且可以通过Workbook#setSheetHidden(int, boolean)进行隐藏(org.apache.poi.ss.usermodel.Workbook)；
 *         第二步：将主体sheet页的列分别与国家省市区的数据进行结合；
 *         第三步：将联动效果通过DataValidationConstraint、DataValidation、addValidationData的方式添加上去，具体见下面代码。
 * @author weibo.xie
 * @date:2022/8/10 下午5:06
 */
public class CascadeWriteHandler implements SheetWriteHandler {
    /** 联动数据源 */
    private List<CascadeExcelDTO> list;
    /**
     *  父节点code
     */
    private final  String parentCode ;

    /**
     *  级联的第一个节点开始列，从0开始
     */
    private int parentColIndex;
    // 几层，省市区就是3层
    private int maxLayer;
    // 创建级联的行数
    private int lastRow ;
    // 级联的sheet是否隐藏
    private boolean isHideSheet ;


    private Map<String, SelectBoxExcelDTO> map = Maps.newLinkedHashMap();
    private TallyMap<Integer> colMap=new TallyMap<>();
    public CascadeWriteHandler(List<CascadeExcelDTO> list, int parentColIndex, String parentCode, int maxLayer) {
        this(list,parentColIndex,parentCode,maxLayer,200,false);
    }

    public CascadeWriteHandler(List<CascadeExcelDTO> list, int parentColIndex, String parentCode, int maxLayer,int lastRow,boolean isHideSheet) {
        this.list = list;
        this.parentColIndex=parentColIndex;
        this.parentCode=parentCode ;
        this.maxLayer =maxLayer;
        this.lastRow=lastRow;
        this.isHideSheet=isHideSheet;
    }


    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
     //   XSSFWorkbook workbook = new XSSFWorkbook();// 创建一个Excel文件


        // 获取一个workbook
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        // 定义sheet的名称
        // 国家

        Sheet[] hideSheet = new Sheet[maxLayer];
        // 根据层级初始化sheet
        for(int i = 0; i< maxLayer; i++){
            Sheet sheet = workbook.createSheet();
            hideSheet[i]=sheet;
            if(isHideSheet){
                workbook.setSheetHidden(workbook.getSheetIndex(sheet),isHideSheet);
            }

        }

       cascadeSheetData(hideSheet,parentCode,null,0);
       // 下拉框关联sheet页
        Name first = workbook.createName();
        String sheetName = hideSheet[0].getSheetName();
        first.setNameName(sheetName);
        first.setRefersToFormula(sheetName + "!$A$1:$A$" + colMap.get(0));
        map.forEach((k,v)->{
            if(v.getName()!=null) {  // 第一层已经设置
                Name areaName = workbook.createName();
                areaName.setNameName(v.getName());
                areaName.setRefersToFormula(v.getSheetName() + "!$A$" + v.getStart() + ":$A$" + v.getEnd());
            }
        });


        // 联动关联
        cellDefinition(writeSheetHolder, helper, first.getSheetName(), parentColIndex);
        for(int i = 1; i< maxLayer; i++){
             int col=parentColIndex+i ;
            cellDefinition(writeSheetHolder, helper, String.format("INDIRECT($%s2)",Excel.getColName(col-1)), col);
        }
    }

    // 联动下拉框，文件1-50行配置，第0行是表头
    private void cellDefinition(WriteSheetHolder writeSheetHolder, DataValidationHelper helper, String country, int col) {
        CellRangeAddressList countryList = new CellRangeAddressList(1, lastRow, col, col);
        DataValidationConstraint countryConstraint = helper.createFormulaListConstraint(country);
        DataValidation countryValidation = helper.createValidation(countryConstraint, countryList);
        writeSheetHolder.getSheet().addValidationData(countryValidation);
    }

   private void cascadeSheetData(Sheet[] hideSheet,String parentCode,String name,int sheetIndex){
       List<CascadeExcelDTO> cascadeList = list.stream().filter(e -> parentCode.equals(e.getParentCode())).collect(Collectors.toList());
       if(DataUtil.isEmpty(cascadeList)){
           return;
       }
       SelectBoxExcelDTO selectBoxExcelDTO=new SelectBoxExcelDTO();
       Integer rowCount = Optional.ofNullable(colMap.get(sheetIndex)).orElse(0);
       selectBoxExcelDTO.setStart(rowCount+1);
       selectBoxExcelDTO.setEnd(rowCount+cascadeList.size());
       selectBoxExcelDTO.setSheetName(hideSheet[sheetIndex].getSheetName());
       selectBoxExcelDTO.setName(name);
       colMap.add(sheetIndex,cascadeList.size());
       map.put(parentCode,selectBoxExcelDTO);
       for(int i = 0; i < cascadeList.size(); i++){
           CascadeExcelDTO cascadeExcel = cascadeList.get(i);
           hideSheet[sheetIndex].createRow(i+rowCount).createCell(0).setCellValue(cascadeExcel.getName());
           if(sheetIndex< maxLayer){
               cascadeSheetData(hideSheet,cascadeExcel.getCode(),cascadeExcel.getName(),sheetIndex+1);
           }

       }




   }











}









